"""
xuanzang空间包含统计脚本
Author: loubenke
Date: 2024-09-11
"""

import pymysql
import psycopg2
import json

pcgis_mysql_conn_config = {}
xz_mysql_conn_config = {}
pg_conn_config = {}
cell_info = {}
comm_info = {}
street_info = {}
dist_info = {}
city_info = {}
sceneUid = ""


def get_sub_layer_info(service_id):
    """
    查询已经绑定大小类的部件信息
    :param service_id: 矢量服务id
    :return:
    """

    sql = """
        SELECT
            vector_layer.id,
            vector_layer.phy_layer_id,
            lower( vector_layer.tb_name ) as tb_name,
            vector_layer.sub_unique_code,
            vector_layer.sub_type_name,
            vector_layer.main_unique_code,
            layer_group.main_type_name 
        FROM
            gis_vector_layer vector_layer
            LEFT JOIN gis_vector_layer_group layer_group ON layer_group.id = vector_layer.group_id 
        WHERE
            layer_group.main_type_name IS NOT NULL 
            AND vector_layer.service_id = {0} 
            AND vector_layer.part_flag != 0
            AND vector_layer.sub_unique_code IS NOT NULL ;
    """.format(service_id)
    print("获取已经绑定小类的图层信息")

    connection = get_xz_mysql_connect()
    cursor = connection.cursor()
    cursor.execute(sql)
    all_rec = cursor.fetchall()

    return all_rec


def gene_part_region_stat(grid_layer_name, grid_usage_code, grid_usage_name, grid_layer_type, key_field, service_id,
                          pack_id):
    # 建立数据库连接
    pg_con = get_pg_connect()
    contains_sql = '''
        WITH _containsInfo AS (
            SELECT
                b."{10}",
                COUNT (*) AS num 
            FROM
                "{0}" A, -- 部件名称
                "{1}" b -- 网格名称
            WHERE
                st_contains ( b.geom, A.geom ) 
            GROUP BY
                b."{10}"
            ) insert into gis_part_region_stat (region_id, region_code, region_name, usage_id, sub_unique_code, sub_type_name, 
                main_unique_code, main_type_name, num, layer_id, geom_type, pack_id, region_type)
            SELECT
            region_id,
            code,
            name,
            '{3}' as usage_id, -- 网格的图层用途号
            '{4}' as sub_unique_code, -- 部件所属小类编码
            '{5}' as sub_type_name, -- 部件所属小类名称
            '{6}' as main_unique_code, -- 部件所属大类编码
            '{7}' as main_type_name, -- 部件所属大类名称
            info.num as nums, -- 包含部件数量
            {8} as phy_layer_id, -- 部件图层号
            1 as geom_type, -- 部件几何类型         
            {11} as pack_id, -- 项目包id       
            {9} as region_type -- 项目包id       
        FROM
            gis_region region
            LEFT JOIN _containsInfo info ON region.code = info.{10} 
        WHERE
            region.type = {9} 
            and info.num is not null ;
            '''
    # 调用游标对象
    cur = pg_con.cursor()
    sub_layer_info = get_sub_layer_info(service_id)
    print("开始生成 {0} 的部件统计数据".format(grid_layer_name))

    for row in sub_layer_info:
        id, phy_layer_id, tb_name, sub_unique_code, sub_type_name, main_unique_code, main_type_name = row
        contains_query_sql = contains_sql.format(
            tb_name,
            grid_layer_name,
            grid_usage_name,
            grid_usage_code,
            sub_unique_code,
            sub_type_name,
            main_unique_code,
            main_type_name,
            phy_layer_id,
            grid_layer_type,
            key_field,
            pack_id
        )
        print("{0} -> {1} 部件统计数据生成...".format(grid_layer_name, tb_name))
        cur.execute(contains_query_sql)
        print("{0} -> {1} 部件统计数据生成完成，提交事务".format(grid_layer_name, tb_name))
        pg_con.commit()
    pg_con.close()
    print("{0} 部件统计数据生成完成".format(grid_layer_name))


def gene_part_all(service_id):
    """
    将指定矢量服务id的、已经绑定大小类的图层合并到一张表中，合并后的名称为part_all

    :param service_id:
    :return:
    """
    insert_sql = """
    INSERT INTO public.part_all (
        objid,
        table_name,
        org_gid,
        objname,
        deptcode1,
        deptname1,
        deptcode2,
        deptname2,
        deptcode3,
        deptname3,
        bgid,
        objstate,
        ordate,
        chdate,
        datasource,
        remark,
        material,
        form,
        locatedsc,
        photo1,
        photo2,
        stcode,
        qrcode,
        deptnumber,
        setup,
        update,
        warranty,
        comname,
        comtel,
        ownername,
        ownertel,
        mainname,
        maintel,
        deptname4,
        liablename,
        liabletel,
        deptname5,
        lawname,
        lawtel,
        filename,
        geom,
        _distcode,
        _streetcode,
        _commcode,
        _cellcode,
        sub_unique_code,
        sub_type_name,
        main_unique_code,
        main_type_name
    ) SELECT
        objid, 
        '{0}' AS tableName,
        gid,
        objname,
        deptcode1,
        deptname1,
        deptcode2,
        deptname2,
        deptcode3,
        deptname3,
        bgid,
        objstate,
        ordate,
        chdate,
        datasource,
        remark,
        material,
        form,
        locatedsc,
        photo1,
        photo2,
        stcode,
        qrcode,
        deptnumber,
        setup,
        update_,
        warranty,
        comname,
        comtel,
        ownername,
        ownertel,
        mainname,
        maintel,
        deptname4,
        liablename,
        liabletel,
        deptname5,
        lawname,
        lawtel,
        filename,
        st_force2d(geom),
        _distcode,
        _streetcode,
        _commcode,
        _cellcode,
        '{1}',
        '{2}',
        '{3}',
        '{4}'
    FROM
        {0}
    """
    pg_con = get_pg_connect()
    cur = pg_con.cursor()
    check_part_all_exist_on_pg()
    cur.execute("TRUNCATE part_all")
    sub_layer_info = get_sub_layer_info(service_id)

    for row in sub_layer_info:
        id, phy_layer_id, tb_name, sub_unique_code, sub_type_name, main_unique_code, main_type_name = row
        check_part_code_on_pg(tb_name)
        sql = insert_sql.format(tb_name, sub_unique_code, sub_type_name, main_unique_code, main_type_name)
        print("执行 %s 表数据向 part_all 表插入")
        cur.execute(sql)
    pg_con.commit()
    pg_con.close()


def sync_xz_region_to_pg(pack_id):
    """
    同步MySQL表中的gis_region到pg库
    :param pack_id:
    :return:
    """
    select_region_sql = """
        select code, name, type, senior_id, coordinate_x, coordinate_y, pack_id, senior_code, region_id from gis_region 
        where pack_id = {0}
    """.format(pack_id)
    insert_region_sql = """
        insert into gis_region (code, name, type, senior_id, coordinate_x, coordinate_y, pack_id, senior_code, region_id) 
        values (%s, %s, %s, %s, %s, %s, %s, %s, %s)
    """
    print("同步MySQL表中的gis_region到pg库,用于统计生成")

    check_region_exist_on_pg()

    pg_con = get_pg_connect()
    pg_cur = pg_con.cursor()
    pg_cur.execute("TRUNCATE gis_region")
    pg_con.commit()

    mysql_conn = get_xz_mysql_connect()
    mysql_cur = mysql_conn.cursor()

    mysql_cur.execute(select_region_sql)
    all_regions = mysql_cur.fetchall()

    pg_cur.executemany(insert_region_sql, all_regions)
    pg_con.commit()
    pg_con.close()

    mysql_conn.close()
    print("同步MySQL表中的gis_region到pg库成功")


def sync_eUrbanGIS_region_stat(pack_id):
    """
    同步PG表中的gis_part_region_stat到eUrbanGIS业务库MySQL中tc_spatial_contain_stat表
    :param pack_id:
    :return:
    """
    select_stat_sql = """
        select 
            region_id, 
            region_code, 
            region_name, 
            region_type, 
            usage_id, 
            sub_unique_code, 
            sub_type_name, 
            main_unique_code,
            main_type_name,
            layer_id,
            geom_type,
            num 
        from 
            gis_part_region_stat where pack_id = {0}
    """.format(pack_id)
    insert_stat_sql = """
        insert into tc_spatial_contain_stat (
            region_id, 
            region_code, 
            region_name, 
            region_type_name, 
            usage_id, 
            sub_unique_code, 
            sub_type_name, 
            main_unique_code, 
            main_type_name, 
            phy_layer_id, 
            geom_type,
            nums
        ) 
        values 
            (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)
    """

    pg_con = get_pg_connect()
    pg_cur = pg_con.cursor()

    mysql_conn = get_cgdb_connect()
    mysql_cur = mysql_conn.cursor()
    print("清空eUrbanGIS业务库中的 tc_spatial_contain_stat表")
    mysql_cur.execute("TRUNCATE tc_spatial_contain_stat")

    pg_cur.execute(select_stat_sql)
    all_stats = pg_cur.fetchall()
    mysql_cur.executemany(insert_stat_sql, all_stats)
    mysql_conn.commit()
    mysql_conn.close()

    pg_con.close()
    print("eUrbanGIS业务库中的 tc_spatial_contain_stat表同步成功")


def sync_pg_region_stat_to_xz(pack_id):
    """
    同步PG表中的gis_part_region_stat到xuanzang业务库MySQL中gis_part_region_stat表
    :param pack_id:
    :return:
    """
    select_stat_sql = """
        select 
            region_id, 
            region_code, 
            region_name, 
            region_type, 
            usage_id, 
            sub_unique_code, 
            sub_type_name, 
            main_unique_code,
            main_type_name,
            layer_id,
            geom_type,
            num,
            pack_id
        from 
            gis_part_region_stat where pack_id = {0}
    """.format(pack_id)
    insert_stat_sql = """
        insert into gis_part_region_stat (
            region_id, 
            region_code, 
            region_name, 
            region_type, 
            usage_id, 
            sub_unique_code, 
            sub_type_name, 
            main_unique_code,
            main_type_name,
            layer_id,
            geom_type,
            num,
            pack_id
        ) 
        values 
            (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)
    """

    pg_con = get_pg_connect()
    pg_cur = pg_con.cursor()

    mysql_conn = get_xz_mysql_connect()
    mysql_cur = mysql_conn.cursor()
    print("删除玄藏业务库表 gis_part_region_stat 中的指定packId下的数据")
    mysql_cur.execute("delete from  gis_part_region_stat where pack_id={0}".format(pack_id))

    pg_cur.execute(select_stat_sql)
    all_stats = pg_cur.fetchall()
    print("向玄藏业务库表 gis_part_region_stat 写入数据")
    mysql_cur.executemany(insert_stat_sql, all_stats)
    mysql_conn.commit()
    mysql_conn.close()

    pg_con.close()
    print("向玄藏业务库表 gis_part_region_stat 写入数完成")


def check_region_exist_on_pg():
    # 如果pg中不存在gis_region表，则进行创建
    pg_con = get_pg_connect()
    cur = pg_con.cursor()
    create_sql = """
    CREATE TABLE "public"."gis_region" (
      "id" serial NOT NULL,
      "code" varchar(40) COLLATE "pg_catalog"."default" NOT NULL,
      "name" varchar(50) COLLATE "pg_catalog"."default" NOT NULL,
      "type" int2 NOT NULL,
      "senior_id" int4 NOT NULL,
      "coordinate_x" float8,
      "coordinate_y" float8,
      "pack_id" int4,
      "senior_code" varchar(40) COLLATE "pg_catalog"."default" NOT NULL,
      "region_id" int4,
      CONSTRAINT "gis_region_pkey" PRIMARY KEY ("id")
    );
    """

    cur.execute("SELECT 1 FROM pg_tables WHERE schemaname = 'public' AND tablename = 'gis_region'")
    all_rec = cur.fetchall()
    if len(all_rec) == 0:
        print("pg库中不存在 gis_region 表，新建...")
        cur.execute(create_sql)
        print("pg库中 gis_region 新建成功")
        pg_con.commit()
    else:
        print("pg库中已经存在 gis_region 表，跳过新建")

    pg_con.close()


def check_part_all_exist_on_pg():
    """
    场景检查pg库中是否存在part_all表，不存在则新建，并创建索引
    :return:
    """
    # 如果pg中不存在part_all表，则进行创建
    pg_con = get_pg_connect()
    cur = pg_con.cursor()
    create_sql = """
    CREATE TABLE "public"."part_all" (
      "gid" SERIAL NOT NULL,
      "table_name" varchar(30) COLLATE "pg_catalog"."default",
      "org_gid" int4,
      "objid" varchar(16) COLLATE "pg_catalog"."default",
      "objname" varchar(30) COLLATE "pg_catalog"."default",
      "deptcode1" varchar(18) COLLATE "pg_catalog"."default",
      "deptname1" varchar(100) COLLATE "pg_catalog"."default",
      "deptcode2" varchar(18) COLLATE "pg_catalog"."default",
      "deptname2" varchar(100) COLLATE "pg_catalog"."default",
      "deptcode3" varchar(18) COLLATE "pg_catalog"."default",
      "deptname3" varchar(100) COLLATE "pg_catalog"."default",
      "bgid" varchar(15) COLLATE "pg_catalog"."default",
      "objstate" varchar(30) COLLATE "pg_catalog"."default",
      "ordate" date,
      "chdate" date,
      "datasource" varchar(30) COLLATE "pg_catalog"."default",
      "remark" varchar(100) COLLATE "pg_catalog"."default",
      "material" varchar(60) COLLATE "pg_catalog"."default",
      "form" varchar(60) COLLATE "pg_catalog"."default",
      "locatedsc" varchar(200) COLLATE "pg_catalog"."default",
      "photo1" varchar(200) COLLATE "pg_catalog"."default",
      "photo2" varchar(200) COLLATE "pg_catalog"."default",
      "stcode" varchar(16) COLLATE "pg_catalog"."default",
      "qrcode" varchar(200) COLLATE "pg_catalog"."default",
      "deptnumber" varchar(200) COLLATE "pg_catalog"."default",
      "setup" varchar(200) COLLATE "pg_catalog"."default",
      "update" varchar(200) COLLATE "pg_catalog"."default",
      "warranty" varchar(200) COLLATE "pg_catalog"."default",
      "comname" varchar(60) COLLATE "pg_catalog"."default",
      "comtel" varchar(11) COLLATE "pg_catalog"."default",
      "ownername" varchar(60) COLLATE "pg_catalog"."default",
      "ownertel" varchar(11) COLLATE "pg_catalog"."default",
      "mainname" varchar(60) COLLATE "pg_catalog"."default",
      "maintel" varchar(11) COLLATE "pg_catalog"."default",
      "deptname4" varchar(60) COLLATE "pg_catalog"."default",
      "liablename" varchar(60) COLLATE "pg_catalog"."default",
      "liabletel" varchar(11) COLLATE "pg_catalog"."default",
      "deptname5" varchar(60) COLLATE "pg_catalog"."default",
      "lawname" varchar(60) COLLATE "pg_catalog"."default",
      "lawtel" varchar(11) COLLATE "pg_catalog"."default",
      "filename" varchar(50) COLLATE "pg_catalog"."default",
      "geom" geometry(GEOMETRY),
      "_distcode" varchar COLLATE "pg_catalog"."default",
      "_streetcode" varchar COLLATE "pg_catalog"."default",
      "_commcode" varchar COLLATE "pg_catalog"."default",
      "_cellcode" varchar COLLATE "pg_catalog"."default",
      "sub_unique_code" varchar(50) COLLATE "pg_catalog"."default",
      "main_unique_code" varchar(50) COLLATE "pg_catalog"."default",
      "sub_type_name" varchar(50) COLLATE "pg_catalog"."default",
      "main_type_name" varchar(50) COLLATE "pg_catalog"."default",
      PRIMARY KEY(table_name, org_gid)
    )
    """

    create_cell_code_index_sql = """
        CREATE INDEX if not exists "part_all_cellcode_index" ON "public"."part_all" USING btree (
          "_cellcode" COLLATE "pg_catalog"."default" "pg_catalog"."text_ops" ASC NULLS LAST
        );
    """
    create_comm_code_index_sql = """
        CREATE INDEX if not exists "part_all_commcode_index" ON "public"."part_all" USING btree (
          "_commcode" COLLATE "pg_catalog"."default" "pg_catalog"."text_ops" ASC NULLS LAST
        );
    """
    create_dist_code_index_sql = """
        CREATE INDEX if not exists "part_all_distcode_index" ON "public"."part_all" USING btree (
          "_distcode" COLLATE "pg_catalog"."default" "pg_catalog"."text_ops" ASC NULLS LAST
        );
    """
    create_street_code_index_sql = """
        CREATE INDEX if not exists "part_all_streetcode_index" ON "public"."part_all" USING btree (
          "_streetcode" COLLATE "pg_catalog"."default" "pg_catalog"."text_ops" ASC NULLS LAST
        );
    """
    create_deptname2_code_index_sql = """
        CREATE INDEX if not exists "part_all_deptname2_index" ON "public"."part_all" USING btree (
          "deptname2" COLLATE "pg_catalog"."default" "pg_catalog"."text_ops" ASC NULLS LAST
        );
    """
    create_deptname1_code_index_sql = """
        CREATE INDEX if not exists "part_all_deptname1_index" ON "public"."part_all" USING btree (
          "deptname1" COLLATE "pg_catalog"."default" "pg_catalog"."text_ops" ASC NULLS LAST
        );
    """
    cur.execute("SELECT 1 FROM pg_tables WHERE schemaname = 'public' AND tablename = 'part_all'")
    all_rec = cur.fetchall()
    if len(all_rec) == 0:
        print("pg库中不存在 part_all 表，新建...")
        cur.execute(create_sql)
        print("pg库中 part_all 新建成功")

        print("对 %s 字段创建索引,sql:%s" % ("_cellcode", create_cell_code_index_sql))
        cur.execute(create_cell_code_index_sql)

        print("对 %s 字段创建索引,sql:%s" % ("_commcode", create_comm_code_index_sql))
        cur.execute(create_comm_code_index_sql)

        print("对 %s 字段创建索引,sql:%s" % ("_streetcode", create_street_code_index_sql))
        cur.execute(create_street_code_index_sql)

        print("对 %s 字段创建索引,sql:%s" % ("_distcode", create_dist_code_index_sql))
        cur.execute(create_dist_code_index_sql)

        print("对 %s 字段创建索引,sql:%s" % ("deptname1", create_deptname1_code_index_sql))
        cur.execute(create_deptname1_code_index_sql)

        print("对 %s 字段创建索引,sql:%s" % ("deptname2", create_deptname2_code_index_sql))
        cur.execute(create_deptname2_code_index_sql)

        pg_con.commit()
    else:
        print("pg库中已经存在 part_all 表，跳过新建")

    pg_con.close()


def check_part_code_on_pg(table_name, ):
    # 如果pg中部件表不存在 _distcode _streetcode _commcode _cellcode字段，新增并复制
    pg_con = get_pg_connect()
    cur = pg_con.cursor()

    dist_code_exist_sql = """
        SELECT 1 FROM information_schema.columns WHERE table_name = '{0}' AND column_name = '_distcode'
    """.format(table_name)

    add_dist_code_sql = """
        ALTER table "{0}" add column _distcode varchar(100) COLLATE "pg_catalog"."default";
    """.format(table_name)

    update_dist_code_sql = """
        update "{0}" a set _distcode = b.{1} from "{2}" b where st_intersects(a.geom, b.geom);
    """.format(table_name, dist_info.get("keyFieldName"), dist_info.get("name"))

    street_code_exist_sql = """
        SELECT 1 FROM information_schema.columns WHERE table_name = '{0}' AND column_name = '_streetcode'
    """.format(table_name)

    add_street_code_sql = """
        ALTER table "{0}" add column _streetcode varchar(100) COLLATE "pg_catalog"."default";
    """.format(table_name)

    update_street_code_sql = """
        update "{0}" a set _streetcode = b.{1} from "{2}" b where st_intersects(a.geom, b.geom);
    """.format(table_name, street_info.get("keyFieldName"), street_info.get("name"))

    comm_code_exist_sql = """
        SELECT 1 FROM information_schema.columns WHERE table_name = '{0}' AND column_name = '_commcode'
    """.format(table_name)

    add_comm_code_sql = """
        ALTER table "{0}" add column _commcode varchar(100) COLLATE "pg_catalog"."default";
    """.format(table_name)

    update_comm_code_sql = """
        update "{0}" a set _commcode = b.{1} from {2} b where st_intersects(a.geom, b.geom);
    """.format(table_name, comm_info.get("keyFieldName"), comm_info.get("name"))

    cell_code_exist_sql = """
        SELECT 1 FROM information_schema.columns WHERE table_name = '{0}' AND column_name = '_cellcode'
    """.format(table_name)

    add_cell_code_sql = """
        ALTER table "{0}" add column _cellcode varchar(100) COLLATE "pg_catalog"."default";
    """.format(table_name)

    update_cell_code_sql = """
        update "{0}" a set _cellcode = b.{1} from {2} b where st_intersects(a.geom, b.geom);
    """.format(table_name, cell_info.get("keyFieldName"), cell_info.get("name"))

    cur.execute(dist_code_exist_sql)
    all_rec = cur.fetchall()
    if len(all_rec) == 0:
        print("%s 表中不存在 _distcode 字段，新增并赋值..." % table_name)
        cur.execute(add_dist_code_sql)
        cur.execute(update_dist_code_sql)
        print("部件表 %s 新增_distcode 字段并赋值成功 " % table_name)
        pg_con.commit()
    else:
        print("%s 表中已经存在 _distcode 字段，跳过新建及赋值" % table_name)

    cur.execute(street_code_exist_sql)
    all_rec = cur.fetchall()
    if len(all_rec) == 0:
        print("%s 表中不存在 _streetcode 字段，新增并赋值..." % table_name)
        cur.execute(add_street_code_sql)
        cur.execute(update_street_code_sql)
        print("部件表 %s 新增 _streetcode 字段并赋值成功 " % table_name)
        pg_con.commit()
    else:
        print("%s 表中已经存在 _streetcode 字段，跳过新建及赋值" % table_name)

    cur.execute(comm_code_exist_sql)
    all_rec = cur.fetchall()
    if len(all_rec) == 0:
        print("%s 表中不存在 _commcode 字段，新增并赋值..." % table_name)
        cur.execute(add_comm_code_sql)
        cur.execute(update_comm_code_sql)
        print("部件表 %s 新增 _commcode 字段并赋值成功 " % table_name)
        pg_con.commit()
    else:
        print("%s 表中已经存在 _commcode 字段，跳过新建及赋值" % table_name)

    cur.execute(cell_code_exist_sql)
    all_rec = cur.fetchall()
    if len(all_rec) == 0:
        print("%s 表中不存在 _cellcode 字段，新增并赋值..." % table_name)
        cur.execute(add_cell_code_sql)
        cur.execute(update_cell_code_sql)
        print("部件表 %s 新增 _cellcode 字段并赋值成功 " % table_name)
        pg_con.commit()
    else:
        print("%s  表中已经存在 _cellcode 字段，跳过新建及赋值" % table_name)

    pg_con.close()


def check_region_stat_exist_on_pg():
    # 如果pg中不存在gis_part_region_stat表，则进行创建
    pg_con = get_pg_connect()
    cur = pg_con.cursor()
    create_sql = """
    CREATE TABLE public.gis_part_region_stat(
        "id" serial NOT NULL,
        "region_id" int4 NOT NULL,
        "region_code" varchar(40) COLLATE "pg_catalog"."default",
        "region_name" varchar(50) COLLATE "pg_catalog"."default",
        "region_type" int2,
        "usage_id" int4,
        "sub_unique_code" varchar(50) COLLATE "pg_catalog"."default",
        "sub_type_name" varchar(50) COLLATE "pg_catalog"."default",
        "main_unique_code" varchar(50) COLLATE "pg_catalog"."default",
        "main_type_name" varchar(50) COLLATE "pg_catalog"."default",
        "layer_id" int4,
        "geom_type" int2,
        "num" int4,
        "pack_id" int4
    );
    """

    cur.execute("SELECT 1 FROM pg_tables WHERE schemaname = 'public' AND tablename = 'gis_part_region_stat'")
    all_rec = cur.fetchall()
    if len(all_rec) == 0:
        print("pg库中不存在 gis_part_region_stat 表，新建...")
        cur.execute(create_sql)
        print("pg库中 gis_part_region_stat 新建成功")
    else:
        print("pg库中已经存在gis_part_region_stat 表，进行数据清空")
        truncate_pg_stat_table()
    pg_con.commit()
    pg_con.close()


def get_pg_connect():
    """
    建立数据库连接(库名称，用户名，密码，主机ip，端口)根据实际进行修改
    :return:
    """
    pg_con = psycopg2.connect(database=pg_conn_config.get("database"),
                              user=pg_conn_config.get("user"),
                              password=pg_conn_config.get("password"),
                              host=pg_conn_config.get("host"),
                              port=pg_conn_config.get("port"))
    return pg_con


def get_xz_mysql_connect():
    """
    建立数据库连接(库名称，用户名，密码，主机ip，端口)根据实际进行修改
    :return:
    """
    connection = pymysql.connect(host=xz_mysql_conn_config.get("host"),
                                 database=xz_mysql_conn_config.get("database"),
                                 user=xz_mysql_conn_config.get("user"),
                                 password=xz_mysql_conn_config.get("password"),
                                 port=xz_mysql_conn_config.get("port")
                                 )
    return connection


def get_cgdb_connect():
    """
    建立数据库连接(库名称，用户名，密码，主机ip，端口)根据实际进行修改
    :return:
    """
    # connection = pymysql.connect(host="127.0.0.1", database="cgdb", user="root", password="egova", port=3306)
    connection = pymysql.connect(host=pcgis_mysql_conn_config.get("host"),
                                 database=pcgis_mysql_conn_config.get("database"),
                                 user=pcgis_mysql_conn_config.get("user"),
                                 password=pcgis_mysql_conn_config.get("password"),
                                 port=pcgis_mysql_conn_config.get("port")
                                 )
    return connection


def truncate_pg_stat_table():
    pg_con = get_pg_connect()
    cur = pg_con.cursor()
    # 先清空pg表中的 gis_part_region_stat
    print("TRUNCATE gis_part_region_stat")
    cur.execute("TRUNCATE gis_part_region_stat")
    print("提交清空表事务")
    pg_con.commit()
    pg_con.close()
    print("PG库中gis_part_region_stat表清空执行完成")


def get_pack_id_with_scene_uid(scene_uid):
    mysql_con = get_xz_mysql_connect()
    cur = mysql_con.cursor()
    cur.execute("select pack_id, name from gis_scene where uid='{0}'".format(scene_uid))
    all_rec = cur.fetchall()
    if len(all_rec) == 0:
        raise SystemExit("不存在指定uid的场景")

    pack_id, name = all_rec[0]

    query_vector_service_sql = "select id, name from gis_vector_service where pack_id={0}".format(pack_id)
    cur.execute(query_vector_service_sql)
    service_rec = cur.fetchall()
    id_num = 0
    if len(service_rec) == 0:
        raise SystemExit("场景下使用的项目包中未配置矢量服务")
    elif len(service_rec) > 1:
        print("场景下使用的项目包中配置了多个矢量服务，暂支持单个服务的统计")
        for i in range(len(service_rec)):
            print(i, service_rec[i][1])
        try:
            user_select = input("请选择需要统计的矢量服务(id)：")
            id_num = int(user_select)
            if id_num < 0 or id_num > len(service_rec) - 1:
                raise SystemExit("选择无效,退出程序")

        except ValueError:
            raise SystemExit("输入非数字，退出程序")

    service_id, service_name = service_rec[id_num]
    return pack_id, name, service_id


def read_config_json():
    with open("config.json", 'r', encoding="UTF-8") as file:
        data = json.load(file)
        return data


def init():
    try:
        config = read_config_json()
        global pcgis_mysql_conn_config
        pcgis_mysql_conn_config = config.get("pcgis_mysql_connection")
        global xz_mysql_conn_config
        xz_mysql_conn_config = config.get("xz_mysql_connection")
        global pg_conn_config
        pg_conn_config = config.get("pg_conn_config")
        global cell_info
        cell_info = config.get("cell_info")
        global comm_info
        comm_info = config.get("comm_info")
        global street_info
        street_info = config.get("street_info")
        global dist_info
        dist_info = config.get("dist_info")
        global sceneUid
        sceneUid = config.get("sceneUid")
        global city_info
        city_info = config.get("city_info")
    except Exception:
        raise SystemExit("初始化配置参数失败")


def part_all():
    init()
    pack_id, pack_name, service_id = get_pack_id_with_scene_uid(sceneUid)
    gene_part_all(service_id)


def part_region_stat():
    stat_city_sql = """
        insert gis_part_region_stat (
            region_id, 
            region_code, 
            region_name, 
            region_type, 
            usage_id, 
            sub_unique_code, 
            sub_type_name, 
            main_unique_code, 
            main_type_name, 
            layer_id, 
            geom_type, 
            num, 
            pack_id) 
        select 
            {0}, 
            {1} ,
            '{2}',
            {3},
            {4},
            sub_unique_code,
            sub_type_name, 
            main_unique_code,
            main_type_name,
            layer_id,
            geom_type,
            sum(num),
            pack_id
        from
            gis_part_region_stat
        GROUP BY 
            sub_unique_code, sub_type_name, main_unique_code, main_type_name, layer_id
    """.format(city_info.get("regionId"), city_info.get("regionCode"),city_info.get("regionName"),city_info.get("regionType"),city_info.get("usageId"))

    # 初始化连接参数
    init()
    # 获取指定场景id下使用的packId和PackName
    pack_id, pack_name, service_id = get_pack_id_with_scene_uid(sceneUid)

    # 表检查并清空pg库中的 gis_part_region_stat
    check_region_stat_exist_on_pg()

    # 同步MySQL表中的gis_region到pg库
    sync_xz_region_to_pg(pack_id)

    gene_part_region_stat(dist_info.get("name"), dist_info.get("usageId"), dist_info.get("usageName"),
                          dist_info.get("regionType"), dist_info.get("keyFieldName"), service_id, pack_id)

    gene_part_region_stat(street_info.get("name"), street_info.get("usageId"), street_info.get("usageName"),
                          street_info.get("regionType"), street_info.get("keyFieldName"), service_id, pack_id)

    gene_part_region_stat(comm_info.get("name"), comm_info.get("usageId"), comm_info.get("usageName"),
                          comm_info.get("regionType"), comm_info.get("keyFieldName"), service_id, pack_id)

    gene_part_region_stat(cell_info.get("name"), cell_info.get("usageId"), cell_info.get("usageName"),
                          cell_info.get("regionType"), cell_info.get("keyFieldName"), service_id, pack_id)

    print("生成市的统计数据...")
    connection = get_xz_mysql_connect()
    cursor = connection.cursor()
    cursor.execute(stat_city_sql)
    connection.commit()
    connection.close()
    print("生成市的统计数据完成")

    # 同步PG表中的gis_part_region_stat到 xuanzang 业务库MySQL中gis_part_region_stat表
    sync_pg_region_stat_to_xz(pack_id)

    # 开始同步PG表中的gis_part_region_stat到eUrbanGIS业务库MySQL中tc_spatial_contain_stat表
    sync_eUrbanGIS_region_stat(pack_id)
